package main

import (
    `database/sql`
    `fmt`
    _ "github.com/go-sql-driver/mysql"
    `github.com/jmoiron/sqlx`
    `log`
)

/*
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `nick` varchar(64) DEFAULT NULL,
  `country` varchar(128) DEFAULT NULL,
  `province` varchar(64) DEFAULT NULL,
  `city` varchar(64) DEFAULT NULL,
  `img_url` varchar(256) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `create_time` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

*/

/*

CREATE DATABASE `sqlx_test`;

USE `sqlx_test`;

CREATE TABLE `items` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` LONGTEXT NOT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO `items` (name, description) VALUES ('mechanical keyboard', 'good for writing code');
INSERT INTO `items` (name, description) VALUES ('coffe', 'oh yummy!');
INSERT INTO `items` (name, description) VALUES ('emacs', 'your favourite text editor');


*/

type Student struct {
    Id         int            `db:"id"`
    Name       string         `db:"name"`
    Nick       sql.NullString `db:"nick"`
    Country    sql.NullString `db:"country"`
    Province   sql.NullString `db:"province"`
    City       sql.NullString `db:"city"`
    ImgUrl     sql.NullString `db:"img_url"`
    Status     sql.NullInt64  `db:"status"`
    CreateTime sql.NullString `db:"create_time"`
}

var Db *sqlx.DB

func init() {
    dns := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8", "zrd", "123456", "127.0.0.1", "test")
    Db, err := sqlx.Connect("mysql", dns)

    if err != nil {
        fmt.Println("数据库连接失败")
        log.Fatalln(err)
    }
    Db.SetMaxIdleConns(5)
    Db.SetMaxOpenConns(100)
    _ = Db.Close()
}

func main() {

    // QuerySingleResult()
    // QueryPrepare()
    //   QueryRowStructScan()
    QueryMultipleRows()

}

type Item struct {
    Id          int            `db:"id"`
    Name        sql.NullString `db:"name"`
    Description sql.NullString `db:"description"`
}

func QuerySingleResult() {
    type Item struct {
        Id          int            `db:"id"`
        Name        sql.NullString `db:"name"`
        Description sql.NullString `db:"description"`
    }
    db := sqlx.MustConnect("mysql", "zrd:123456@tcp(127.0.0.1:3306)/sqlx_test")
    var item Item
    err := db.Get(&item, "SELECT * FROM items ORDER BY id DESC LIMIT 1")
    if err != nil {
        fmt.Println(err)
        return
    }
    // fmt.Printf("id: %d, %s, %s", item.Id, item.Name.String, item.Description.String)
    fmt.Printf("%#v", item)
}

func QueryPrepare() {

    db := sqlx.MustConnect("mysql", "zrd:123456@tcp(127.0.0.1:3306)/sqlx_test")
    var item Item

    stmt, err := db.Preparex(`SELECT * FROM items WHERE id=?`)

    // 存在一条
    err = stmt.Get(&item, 1)
    fmt.Println(item)

    // not existing one
    err = stmt.Get(&item, 3)
    fmt.Println(item)

    if err == sql.ErrNoRows {
        fmt.Println("不存在此id")
    }
    if err != nil {
        fmt.Println(err)
    }
}

func QueryRowStructScan() {
    db := sqlx.MustConnect("mysql", "zrd:123456@tcp(127.0.0.1:3306)/sqlx_test")
    item := Item{}
    row := db.QueryRowx("SELECT * FROM items WHERE id =?", 3)
    err := row.StructScan(&item)
    if err != nil {
        fmt.Println(err)
    }
    fmt.Printf("%v", item)
}

func QueryMultipleRows() {
    db := sqlx.MustConnect("mysql", "zrd:123456@tcp(127.0.0.1:3306)/sqlx_test")
    var items []Item

    rows, err := db.Queryx("SELECT id, name, description FROM items")
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        var item Item
        err = rows.StructScan(&item)
        if err != nil {
            panic(err)
        }
        items = append(items, item)

    }
    fmt.Printf("%v", items)
    // http://wysocki.in/golang-sqlx/

}

/*// 查询
func query_select(db *sqlx.DB, name string) (err error) {
    var s Student
    err = db.Get(&s, "SELECT * FROM student WHERE name=?", "Emily")
    if err != nil {
        log.Fatalln(err)
    }
    fmt.Println(s)
    fmt.Println("------------------ 多条 ------------------")
    var stus []Student
    db.Select(&stus, "SELECT * FROM student")

    for i, v := range stus {
        fmt.Println(i, v)
    }

    fmt.Println("------------------ 多条 ------------------")

    rows, err := db.Query("SELECT * FROM student")
    if err != nil {
        log.Fatalln(err)
    }
    for rows.Next() {
        var stu1 Student
        err := rows.Scan(&stu1)
        if err != nil {
            log.Fatalln(err)
        }
        fmt.Println(stu1)
    }
    rows.Close()
    return
}

// 事务
func transaction_t(db *sqlx.DB) (err error) {
    return
    tx := db.MustBegin()
    tx.MustExec(`INSERT INTO student VALUES ('Jack', 'Jack', 'England', '', '', 'http://img2.imgtn.bdimg.com/it/u=3588772980,2454248748&fm=27&gp=0.jpg', '1', '2018-06-26 17:08:35');`)
    tx.MustExec(`INSERT INTO student VALUES ( 'Emily', 'Emily', 'England', '', '', 'http://img2.imgtn.bdimg.com/it/u=3588772980,2454248748&fm=27&gp=0.jpg', '2', null);`)
    err = tx.Commit()
    if err != nil {
        log.Fatalln(err)
    }
    return
}

// inseret
func query_insert(db *sqlx.DB) (err error) {
    fmt.Println("--- query_insert  ---")
    // INSERT INTO `t1`.`student` (`name`, `nick`, `country`, `province`, `city`, `img_url`, `status`, `create_time`) VALUES (?,?,?,?,?,?,?,?);
    result, err := db.Exec("INSERT INTO `t1`.`student` (`name`, `nick`, `country`, `province`, `city`, `img_url`, `status`, `create_time`) VALUES (?,?,?,?,?,?,?,?)", "tom", "omcat", "CN", "ff", "f2", `http;//xx.jpg`, '1', "sadf")
    if err != nil {
        fmt.Println("insert failed,", err)
    }
    // 通过LastInsertId可以获取插入数据的id
    userId, err := result.LastInsertId()
    // 通过RowsAffected可以获取受影响的行数
    rowCount, err := result.RowsAffected()

    fmt.Println("user_id:", userId)
    fmt.Println("rowCount:", rowCount)
    return
}
*/
